## Created by Mohsen Jafari on 11/12/11
## Provides to types of filters to query a SQL database. ComplexFilter combines more than one boolean expression using and, or, and not. SimpleFilter is a single
## boolean expression like "username like '%mohsen%'".

class ConditionType(object):
	Equal,\
	GreaterThan,\
	NotEqual,\
	LessThan,\
	GreaterThanOrEqual,\
	LessThanOrEqual,\
	Null,\
	StartWith,\
	EndWith,\
	Contain,\
        In,\
        NotIn = range(12)

class OperatorType(object):
	AND,\
        OR,\
        NOT = range(3)

class SearchFilter(object):
	def __init__(self):
		pass

	def GetWhereClause(self):
		return ""

class SimpleFilter(SearchFilter):
	def __init__(self, fieldname, condition, value):
		self.__fieldName = fieldname
		self.__condition = condition
		self.__value = value

	def __get_FieldName(self):
		return self.__fieldName

	def __set_FieldName(self, value):
		self.__fieldName = value

	FieldName = property(fget=__get_FieldName, fset=__set_FieldName)

	def __get_Value(self):
		return self.__value

	def __set_Value(self, value):
		self.__value = value

	Value = property(fget=__get_Value, fset=__set_Value)

	def __get_Condition(self):
		return self.__condition

	def __set_Condition(self, value):
		self.__condition = value

	Condition = property(fget=__get_Condition, fset=__set_Condition)

	def __ConditionTypeStr(self):
		if self.Condition == ConditionType.Equal:
			return "="
		elif self.Condition == ConditionType.NotEqual:
			return "<>"
		elif self.Condition == ConditionType.GreaterThan:
			return ">"
		elif self.Condition == ConditionType.LessThan:
			return "<"
		elif self.Condition == ConditionType.GreaterThanOrEqual:
			return ">="
		elif self.Condition == ConditionType.LessThanOrEqual:
			return "<="
		elif self.Condition == ConditionType.Null:
			return "IS NULL "
		elif self.Condition == ConditionType.StartWith:
			return "LIKE"
		elif self.Condition == ConditionType.EndWith:
			return "LIKE"
		elif self.Condition == ConditionType.Contain:
			return "LIKE"
		elif self.Condition == ConditionType.In:
			return "in"
		elif self.Condition == ConditionType.NotIn:
			return "not in"
		else:
			return "="

	def __SQLValue(self):
		if self.Condition in (ConditionType.Equal,ConditionType.GreaterThan,ConditionType.LessThan,ConditionType.GreaterThanOrEqual,ConditionType.LessThanOrEqual,ConditionType.NotEqual):
			return "'" + str(self.Value) + "'"
		elif self.Condition == ConditionType.Null:
			return ""
		elif self.Condition == ConditionType.StartWith:
			return "'"+str(self.Value)+"%'"
		elif self.Condition == ConditionType.EndWith:
			return "'%"+str(self.Value)+"'"
		elif self.Condition == ConditionType.Contain:
			return "'%"+str(self.Value)+"%'"
		elif self.Condition == ConditionType.In or self.Condition == ConditionType.NotIn:
			return "(%s)" % str(self.Value)
		else:
			return str(self.Value)

	def GetWhereClause(self):
		if str(self.Value) <> "" or self.Condition == ConditionType.Null:
			return "%s %s %s" % (self.FieldName, self.__ConditionTypeStr(), self.__SQLValue())
		else:
			return ""

class ComplexFilter(SearchFilter):
	def __init__(self, operatorType):
		self.__filters = []
		self.__operator = operatorType

	def __get_Filters(self):
		return self.__filters

	Filters = property(fget=__get_Filters)

	def __get_Operator(self):
		return self.__operator

	def __set_Operator(self, value):
		self.__operator = value

	Operator = property(fget=__get_Operator, fset=__set_Operator)

	def __OperatorStr(self):
		if self.Operator == OperatorType.AND:
			return " AND "
		elif self.Operator == OperatorType.OR:
			return " OR "
		elif self.Operator == OperatorType.NOT:
			return " NOT "
		else:
			return ""

	def GetWhereClause(self):
		if self.Operator == OperatorType.NOT:
			if len(self.Filters) <> 1:
				raise Exception("\"Not\" complex filter accepts only one filter.")
			return "%s (%s)" % (self.__OperatorStr(), self.Filters[0].GetWhereClause())

		whereClause = ""
		for fltr in self.Filters:
			if whereClause <> "":
				whereClause += self.__OperatorStr()
			simpleWhereClause = fltr.GetWhereClause()
			if simpleWhereClause <> "":
				whereClause += " (%s)" % simpleWhereClause
		return whereClause
